/**
 */

package com.bei_en.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.number.NumberFormatter;
import org.springframework.stereotype.Controller;

import com.bei_en.teacher.admin.model.education.condition.EducationCondition;
import com.bei_en.teacher.admin.model.education.vo.EducationVo;
import com.bei_en.teacher.admin.model.questions.vo.QuestionsTypeVo;
import com.bei_en.teacher.admin.model.questions.vo.QuestionsVo;
import com.bei_en.teacher.admin.model.subject.condition.SubjectCondition;
import com.bei_en.teacher.admin.model.subject.vo.SubjectVo;
import com.bei_en.teacher.admin.service.education.EducationService;
import com.bei_en.teacher.admin.service.subject.SubjectService;
import com.bei_en.teacher.management.controller.BaseController;

/**
 * @author Guocg
 *
 */
public class ExcelUtilController {

	public static Map realestateImportExcel(String path, boolean pre)
			throws Exception {

		Map map = new HashMap();
		NPOIFSFileSystem fs = null;
		List<QuestionsVo> list = new ArrayList<QuestionsVo>();
		List<String> wrongList = new ArrayList<String>();
		try {
			fs = new NPOIFSFileSystem(new File(path));
			HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
			// 读取第一页,一般一个excel文件会有三个工作表，这里获取第一个工作表来进行操作
			HSSFSheet sheet = wb.getSheetAt(0);
			// 循环遍历表sheet.getLastRowNum()是获取一个表最后一条记录的记录号，
			// 如果总共有3条记录，那获取到的最后记录号就为2，因为是从0开始的
			int total = sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
			map.put("total", total - 1);
			int right = 0;
			for (Row row : sheet) {
			int num=	row.getRowNum() + 1;
				QuestionsVo vo = new QuestionsVo();
				if (row.getRowNum() > 0 && !row.getZeroHeight()) {
					String s0 = getStr(row, 0);
					if (s0 == null) {
						wrongList.add("第" +num + "行" + "来源为空");
						continue;
					}
					vo.setYear(s0);

					String s1 = getStr(row, 1);
					if (s1 == null) {
						wrongList
								.add("第" +num + "行" + "题类型为空");
						continue;
					}
//					1:'单选题',2:'判断题',3:'多选题',4:'名词解析',5:'简答题',6:'填空题',7:'论述题',8:'材料分析题',9:'辨析题'
					int type=-1;
					try{
						type	=Integer.valueOf(s1);
						vo.setType(Integer.valueOf(s1));
					}catch(NumberFormatException e){
						wrongList
						.add("第" +num + "行" + "题类型格式错误");
						continue;
					}
				

					String s2 = getStr(row, 2);
					if (s2 == null) {
						wrongList.add("第" +num + "行" + "题干为空");
						continue;
					}
					vo.setTitle(s2);
					
					String s3 = getStr(row, 3);
					if (s3 == null&&type!=5&&type!=6&&type!=7&&type!=8&&type!=13&&type!=14&&type!=15&&type!=16&&type!=18&&type!=19&&type!=20&&type!=21&&type!=23&&type!=24&&type!=25&&type!=26) {
						wrongList
								.add("第" +num+ "行" + "题选项为空");
						continue;
					}
					if(s3!=null){
						vo.setContent(s3.trim());
					}else{
						vo.setContent(s3);
					}
					

					String s4 = getStr(row, 4);
					if (s4 == null&&type!=5&&type!=7&&type!=8&&type!=13&&type!=14&&type!=16&&type!=18&&type!=19&&type!=20&&type!=21&&type!=23&&type!=24&&type!=26) {
						wrongList.add("第" +num+ "行" + "答案为空");
						continue;
					}
					if(s4!=null){
						vo.setAnswer(s4.trim());
					}else{
						vo.setAnswer(s4);
					}

					String s5 = getStr(row, 5);
					if (s5 == null&&type!=5&&type!=7&&type!=8&&type!=13&&type!=14&&type!=16&&type!=18&&type!=19&&type!=20&&type!=21&&type!=23&&type!=24&&type!=25&&type!=26) {
						wrongList.add("第" +num + "行"
								+ "答案描述为空");
						continue;
					}
					if(s5!=null){
						vo.setAnswers(s5.trim());
					}else{
						vo.setAnswers(s5);
					}
					String s6 = getStr(row, 6);
					if (s6 == null) {
						wrongList.add("第" +num + "行" + "解析为空");
						continue;
					}
					vo.setAnalysis(s6);
					String s7 = getStr(row, 7);
					if (s7 == null) {
						wrongList.add("第" +num + "行" + "分数为空");
						continue;
					}
					vo.setFraction(Double.valueOf(s7));

					String s8 = getStr(row, 8).trim();
					if (s8 == null||s8.equals("")) {
						wrongList
								.add("第" +num+ "行" + "题id为空");
						continue;
					}
					vo.setQuestionsTypeId(Integer.valueOf(s8.trim()));

					vo.setType2(1);
					vo.setCreateTime(new Date());
					vo.setMakeTime(0);
					vo.setMakeNum(0);
					vo.setYesNum(0);
					vo.setErrorNum(0);
					vo.setWeight(0);
					right++;
					list.add(vo);
				}
			}
			if (!pre)
				map.put("list", list);
			map.put("right", right);
			map.put("wrong", wrongList);// 错误提示
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (fs != null)
				try {
					fs.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}

		return map;
	}

	public Map realestateImportExcel2(String path, boolean pre)
			throws Exception {
		Map map = new HashMap();
		NPOIFSFileSystem fs = null;
		List<QuestionsTypeVo> list = new ArrayList<QuestionsTypeVo>();
		try {
			fs = new NPOIFSFileSystem(new File(path));
			HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
			// 读取第一页,一般一个excel文件会有三个工作表，这里获取第一个工作表来进行操作
			HSSFSheet sheet = wb.getSheetAt(0);
			// 循环遍历表sheet.getLastRowNum()是获取一个表最后一条记录的记录号，
			// 如果总共有3条记录，那获取到的最后记录号就为2，因为是从0开始的
			int total = sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
			List<String> wrongList = new ArrayList<String>();
			map.put("total", total - 1);
			int right = 0;
			
			for (Row row : sheet) {
				int num=	row.getRowNum() + 1;
				QuestionsTypeVo vo = new QuestionsTypeVo();
				if (row.getRowNum() > 0 && !row.getZeroHeight()) {
					String s0 = getStr(row, 0);
					if (s0 == null) {
						wrongList.add("第" +num+ "行" + "名字为空");
						continue;
					}
					vo.setName(s0);
					String s1 = getStr(row, 1);
					if (s1 == null) {
						wrongList
								.add("第" +num+ "行" + "父id为空");
						continue;
					}
					vo.setParent(Integer.valueOf(s1));

					String s2 = getStr(row, 2);
					if (s2 != null) {
						vo.setEducationName(s2);

					}

					String s3 = getStr(row, 3);

					if (s3 != null) {
						vo.setSubjectName(s3);
					}

					if(vo.getEducationName()!=null&&vo.getSubjectName()!=null){
						wrongList.add("第" +num+ "行" + "教育综合和报考科目二选一");
						continue;
					}
					vo.setCreateTime(new Date());
					vo.setMakeTime(0);
					vo.setMakeNum(0);
					vo.setYesNum(0);
					vo.setFractionSum(0l);
					vo.setWeight(0d);
					vo.setMakeFraction(0d);
					vo.setQuestionsSum(0l);
					vo.setStudyNum(0);
					vo.setType(1);
					right++;
					list.add(vo);
				}
			}
			if (!pre)
				map.put("list", list);
			map.put("right", right);
			map.put("wrong", "没");// 错误提示
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (fs != null)
				try {
					fs.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}

		return map;
	}

	public static boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}

	private static String getStr(Row row, int index) {
		// 创建一个行里的一个字段的对象，也就是获取到的一个单元格中的值 Row.CREATE_NULL_AS_BLANK
		// 如果单元格不存在则创建一个新的单元格
		Cell cell = row.getCell(index, Row.CREATE_NULL_AS_BLANK);
		String re = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			System.out.println(index + "空");
			re = null;
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			System.out.println(index + "boolean");
			re = re + cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			System.out.println(index + "FORMULA");
			re = re + cell.getCellFormula();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			System.out.println(index + "NUMERIC");
			re = re + new Double(cell.getNumericCellValue()).intValue();
			break;
		case Cell.CELL_TYPE_STRING:
			System.out.println(index + "STRING");
			re = re + cell.getStringCellValue();
			break;
		}
		return re;
	}
}
